﻿using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using DBUtil;
using DBUtil.Attributes;
using DBUtil.Builders;
using DotNetCommon.DiagnosticReport;
using DotNetCommon.Extensions;
using NUnit.Framework;

namespace Test.MySql
{
    [TestFixture]
    public class TestBase
    {
        protected DBAccess db = null;
        protected void DropTableIfExist(string tableName)
        {
            db.ExecuteSql(db.Manage.DropTableIfExistSql(tableName));
        }
        protected void DropTable(string tableName)
        {
            db.ExecuteSql(db.Manage.DropTableSql(tableName));
        }
        protected void TruncateTable(string tableName)
        {
            db.ExecuteSql($"truncate table {tableName}");
        }

        public TestBase()
        {
            DBUtil.DBFactory.AddDBSupport<DBUtil.Provider.MySql.MySqlDBFactory>();
            //mysql 8.0.12
            //db = DBFactory.CreateDB("MySql", "Server=192.168.0.19;Database=test;Uid=root;Pwd=123456;Charset=utf8;Max Pool Size=20000;");
            //mysql 5.7
            //db = DBFactory.CreateDB("MySql", "Server=192.168.0.18;Database=test;Uid=root;Pwd=123456;Charset=utf8;Max Pool Size=20000;");
            //为了支持 BulkCopy 数据库还需要设置: show variables like '%local_infile%'
            db = DBFactory.CreateDB("MySql", "Server=127.0.0.1;Database=test;Uid=root;Pwd=123456;AllowLoadLocalInfile=true;");
            //db = DBFactory.CreateDB("MySql", "Server=192.168.0.563;Database=test;Uid=root;Pwd=123456;Charset=utf8;Max Pool Size=20000;");

            db = DBFactory.CreateDB("MySql", "Server=127.0.0.1;Database=test;Uid=root;Pwd=123456;AllowLoadLocalInfile=true;SslMode=none;AllowPublicKeyRetrieval=True;Charset=utf8mb4;", DBSetting.NewInstance()
                .SetSqlMonitor(arg =>
                {
                    Console.WriteLine(arg);
                    return Task.CompletedTask;
                })
                .SetWriteMonitor(arg =>
                {
                    Console.WriteLine(arg);
                    return Task.CompletedTask;
                }));

        }

        public void PrepareColumnTypes()
        {
            DropTableIfExist("test");
            db.ExecuteSql(@"CREATE TABLE test (
	t_bigint BIGINT NULL,
	t_bigint_unsigned BIGINT UNSIGNED NULL,
	t_binary BINARY(50) NULL,
	t_bit BIT(32) NULL,
	t_blob BLOB NULL,
	t_bool BOOL NULL,
	t_char CHAR(50) NULL,
	t_date DATE NULL,
	t_datetime DATETIME NULL,
	t_decimal DECIMAL NULL,
	t_double DOUBLE NULL,
	t_double_precision DOUBLE PRECISION NULL,
	t_enum ENUM('reg','green','blue') NULL,
	t_float FLOAT NULL,
	t_int INT NULL,
	t_int_unsigned INT UNSIGNED NULL,
	t_integer INTEGER NULL,
	t_integer_unsigned INTEGER UNSIGNED NULL,
	t_long_varbinary LONG VARBINARY NULL,
	t_long_varchar LONG VARCHAR NULL,
	t_longblob LONGBLOB NULL,
	t_longtext LONGTEXT NULL,
	t_mediumblob MEDIUMBLOB NULL,
	t_mediumint MEDIUMINT NULL,
	t_mediumint_unsigned MEDIUMINT UNSIGNED NULL,
	t_mediumtext MEDIUMTEXT NULL,
	t_numeric NUMERIC NULL,
	t_real REAL NULL,
	t_set SET('red','green','blue') NULL,
	t_smallint SMALLINT NULL,
	t_smallint_unsigned SMALLINT UNSIGNED NULL,
	t_text TEXT NULL,
	t_time TIME NULL,
	t_timestamp TIMESTAMP NULL,
	t_tinyblob TINYBLOB NULL,
	t_tinyint TINYINT NULL,
	t_tinyint_unsigned TINYINT UNSIGNED NULL,
	t_tinytext TINYTEXT NULL,
	t_varbinary VARBINARY(100) NULL,
	t_varchar VARCHAR(100) NULL,
	t_year YEAR NULL,
	t_json json NULL
)");
            var res = db.Insert("test",
                new
                {
                    t_bigint = 1,
                    t_bigint_unsigned = 2,
                    t_binary = Encoding.UTF8.GetBytes("t_binary"),
                    t_bit = 0,
                    t_blob = Encoding.UTF8.GetBytes("t_blob"),
                    t_bool = true,
                    t_char = 'c',
                    t_date = DateTime.Now.ToCommonDateString(),
                    t_datetime = DateTime.Now,
                    t_decimal = 12.354515,
                    t_double = 12.3645,
                    t_double_precision = 14.023,
                    t_enum = "blue",
                    t_float = 0.56326,
                    t_int = 3,
                    t_int_unsigned = 4,
                    t_integer = 5,
                    t_integer_unsigned = 6,
                    t_long_varbinary = Encoding.UTF8.GetBytes("t_long_varbinary"),
                    t_long_varchar = "t_long_varchar",
                    t_longblob = Encoding.UTF8.GetBytes("t_longblob"),
                    t_longtext = "t_longtext",
                    t_mediumblob = Encoding.UTF8.GetBytes("t_mediumblob"),
                    t_mediumint = 7,
                    t_mediumint_unsigned = 8,
                    t_mediumtext = "t_mediumtext",
                    t_numeric = 9.69,
                    t_real = 10.23,
                    t_set = "red,blue",
                    t_smallint = 11,
                    t_smallint_unsigned = 12,
                    t_text = "t_text",
                    t_time = DateTime.Now.ToCommonTimeString(),
                    t_tinyblob = Encoding.UTF8.GetBytes("t_tinyblob"),
                    t_tinyint = 13,
                    t_tinyint_unsigned = 14,
                    t_tinytext = "t_tinytext",
                    t_varbinary = Encoding.UTF8.GetBytes("t_varbinary"),
                    t_varchar = "t_varchar",
                    t_year = DateTime.Now.Year,
                    t_json = new { name = "小明", age = 20 }.ToJson()
                }.ToDictionary()).ExecuteAffrows();
            Assert.IsTrue(res == 1);
        }

        internal InsertBuilder<JsonTestPerson> PrepareJsonTable()
        {
            DropTableIfExist("test");
            db.ExecuteSql("""
                create table test(
                    id int primary key auto_increment,
                    p_byte json,p_sbyte json,
                    p_short json,p_ushort json,
                    p_int json,p_uint json,
                    p_long json,p_ulong json,
                    p_float json,p_double json,p_decimal json,
                    p_long_null json,
                    p_string json,
                    p_datetime json,p_dateonly json,p_timeonly json,p_timespan json,p_datetime_null json,
                    p_guid json,p_guid_null json,
                    p_bool json,p_bool_null json,
                    p_object json,p_object_null json,
                    p_list json,p_arr json,
                    p_dic json
                )
                """);
            return db.Insert<JsonTestPerson>().SetEntity(new JsonTestPerson
            {
                p_byte = 1,
                p_sbyte = 2,
                p_short = 3,
                p_ushort = 4,
                p_int = 5,
                p_uint = 6,
                p_long = 7,
                p_ulong = 8,
                p_float = 9,
                p_double = 10,
                p_decimal = 11,
                p_long_null = 12,
                p_string = "tom",
                p_datetime = DateTime.Parse("2023-07-06 17:32:00"),
                p_dateonly = DateOnly.Parse("2023-07-06"),
                p_timeonly = TimeOnly.Parse("17:32:00"),
                p_timespan = TimeSpan.FromSeconds(3600),
                p_datetime_null = DateTime.Parse("2023-07-06 17:32:00"),
                p_guid = Guid.Parse("96d80686975340dda55492ecda9c92f2"),
                p_guid_null = Guid.Parse("96d80686975340dda55492ecda9c92f2"),
                p_bool = true,
                p_bool_null = true,
                p_object = new { Age = 18, Name = "jim" },
                p_object_null = new { Age = 18, Name = "jim", Other = "oth" },
                p_list = new List<string> { "tom", "lisa" },
                p_arr = new[] { "lisa", "jim" },
                p_dic = new Dictionary<string, object> { { "name", "tom" }, { "age", 18 } }
            });
        }

        public static string PrintTable(DataTable dt)
        {
            var list = new List<Dictionary<string, object>>();
            foreach (DataRow row in dt.Rows)
            {
                var dic = new Dictionary<string, object>();
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    dic.Add(dt.Columns[i].ColumnName, row[i] == DBNull.Value ? null : row[i]);
                }
                list.Add(dic);
            }
            return list.ToJson();
        }
    }

    [Table("test")]
    internal class JsonTestPerson
    {
        [PrimaryKey(KeyStrategy = KeyStrategy.Identity)]
        public int Id { get; set; }

        [JsonStore(Bucket = "p_byte")]
        public byte p_byte { get; set; }

        [JsonStore(Bucket = "p_sbyte")]
        public sbyte p_sbyte { get; set; }

        [JsonStore(Bucket = "p_short")]
        public short p_short { get; set; }

        [JsonStore(Bucket = "p_ushort")]
        public ushort p_ushort { get; set; }

        [JsonStore(Bucket = "p_int")]
        public int p_int { get; set; }

        [JsonStore(Bucket = "p_uint")]
        public uint p_uint { get; set; }

        [JsonStore(Bucket = "p_long")]
        public long p_long { get; set; }

        [JsonStore(Bucket = "p_ulong")]
        public ulong p_ulong { get; set; }

        [JsonStore(Bucket = "p_float")]
        public float p_float { get; set; }

        [JsonStore(Bucket = "p_double")]
        public double p_double { get; set; }

        [JsonStore(Bucket = "p_decimal")]
        public decimal p_decimal { get; set; }

        [JsonStore(Bucket = "p_long_null")]
        public int? p_long_null { get; set; }

        [JsonStore(Bucket = "p_string")]
        public string p_string { get; set; }

        [JsonStore(Bucket = "p_datetime")]
        public DateTime p_datetime { get; set; }

        [JsonStore(Bucket = "p_dateonly")]
        public DateOnly p_dateonly { get; set; }

        [JsonStore(Bucket = "p_timeonly")]
        public TimeOnly p_timeonly { get; set; }

        [JsonStore(Bucket = "p_timespan")]
        public TimeSpan p_timespan { get; set; }

        [JsonStore(Bucket = "p_datetime_null")]
        public DateTime? p_datetime_null { get; set; }

        [JsonStore(Bucket = "p_guid")]
        public Guid p_guid { get; set; }

        [JsonStore(Bucket = "p_guid_null")]
        public Guid? p_guid_null { get; set; }

        [JsonStore(Bucket = "p_bool")]
        public bool p_bool { get; set; }

        [JsonStore(Bucket = "p_bool_null")]
        public bool? p_bool_null { get; set; }

        [JsonStore(Bucket = "p_object")]
        public object p_object { get; set; }


        [JsonStore(Bucket = "p_object_null")]
        public object? p_object_null { get; set; }

        [JsonStore(Bucket = "p_list")]
        public List<string> p_list { get; set; }

        [JsonStore(Bucket = "p_arr")]
        public string[] p_arr { get; set; }

        [JsonStore(Bucket = "p_dic")]
        public Dictionary<string, object> p_dic { get; set; }
    }
}
